![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Direct-Write SortsYou can use the Direct Write Sort option with the Parallel Query option and have the query servers each perform their own direct writes. As you have seen earlier in this chapter, using direct writes causes the server processes to write the output of sort operations directly to disk, bypassing the buffer cache. The effect of direct writes is that, for sort operations, large amounts of block buffers are not ejected from the buffer cache. This leaves the buffer cache available for normal queries and updates. When using direct-write sorts with the Parallel Query option, each query server gets its own set of direct-write buffers. Remember that direct-write sorts take more memory than normal sorts. The amount of memory it uses with the Parallel Query option can be determined with the following formula: Direct Write Sort Memory = ( Number of Query Servers ) * ( SORT_WRITE_BUFFERS ) * ( SORT_WRITE_BUFFER_SIZE ) Only use direct-write sorts if you have sufficient memory and temporary disk space. The temporary disk space should have a sufficient I/O bandwidth to handle the load. Parallel Index CreationAnother feature of the Parallel Query option is its ability to create indexes in parallel. With the parallel index creation feature, the time it takes to create an index can be greatly reduced. Similar to parallel query processing, a coordinator process dispatches two sets of query servers. One set of query servers scans the table to be indexed to obtain the ROWIDs and column values needed for the index. Another set of query servers performs the sorting on those values and passes off the results to the coordinator process. The coordinator process then puts together the B*-tree index from these sorted items. When creating an index, the degree of parallelism follows the same precedence as it does in parallel query processing. The first value used is an optional PARALLEL clause in the CREATE INDEX statement, followed by the table definition, and finally the initialization parameters. Creating an index in parallel can be several times faster than creating an index by normal means. The same conditions apply for index creation as were given for parallel query processing. A system that has been configured to take advantage of parallel query processing will also see good performance from parallel index creation. Parallel LoadingLoading can be done in parallel by having multiple concurrent sessions perform a direct path load into the same table. Depending on the configuration of the system, you can see excellent load performance by loading in parallel. Because loading is both CPU and I/O intensive, in an SMP or MPP environment with a high bandwidth I/O subsystem, you should seed good results. Parallel loads are performed by multiple direct loader processes each using the PARALLEL=TRUE and DIRECT=TRUE options. When you specify PARALLEL=TRUE, the loader does not place an exclusive lock on the table being loaded as in would otherwise. During the parallel load, the loader creates temporary segments for each of the concurrent processes and merges them together on completion. Although parallel loading performs best when each temporary file is located on a separate disk, the increased performance of the load does not usually justify the complexity of the manual striping needed to do this. I still recommend striping the tables on an OS levelor preferably on a hardware disk array (refer to Chapter 15, Disk Arrays). Performance can be improved by putting each of the input files on a separate volume to take advantage of the sequential nature of the reads. Parallel loading can be beneficial, especially if load time is critical in your environment. By putting each of the input files on separate disk volumes, you can increase performance. Overall, the general tuning principles used in parallel query processing are valid in parallel loading also.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |